Amazon Athena がついにINSERT INTOをサポートしたので実際に試してみました！
ついにAmazon Athena がINSERT INTOをサポートしました！これまでは、更新系クエリはCTAS（CREATE TABLE AS）のみでしたが、INSERT INTOによる書き込みクエリがサポートされました。既存のテーブルに対してどのようにデータを追加するのか、どのレベルまでサポートしているのか、気になること満載なので実際に試してみます。
目次
- INSERT INTOのサポート内容
- 構文と例
- データのファイルフォーマット
- INSERT INTO ... VALUEの動作を確認
- パーティションしていないテーブルの検証
- パーティションしたテーブルの検証
- INSERT INTO ... SELECTの動作を確認
- パーティションしていないテーブルからデータの追加
- パーティションしているテーブルからデータの追加
- INSERTしたテーブルの削除
- 利用費
- まとめ
INSERT INTOのサポート内容
構文と例
一般的なレコードのINSERTとSELECTクエリの結果をテーブルに追加できるようになります。
一般的なレコードのINSERT（INSERT INTO ... VALUE）
構文は以下のとおりです。
INSERT INTO destination_table [(col1,col2,...)] VALUES (col1value,col2value,...)[, (col1value,col2value,...)][, ...]
最もシンプルなテーブルに単一のレコードを挿入する例です。
INSERT INTO cities VALUES (1,'San Francisco','CA','Eureka')
テーブルに2つのレコードを挿入する場合は、VALUESに複数レコードの値を指定します。複数レコードの値を指定すると１つのファイルに複数レコードが格納されます。
INSERT INTO cities VALUES (1,'San Francisco','CA','Eureka'), (3,'Boise','ID','Esto perpetua')
SELECTクエリの結果をテーブルに追加（INSERT INTO ... SELECT）
構文は以下のとおりです。
INSERT INTO destination_table SELECT select_query FROM source_table_or_view
vancouver_pageviewsテーブル内のすべてのレコードを選択して、テーブルに挿入しcanada_pageviewsます。SELECT以下はフィルタ、集計した結果をテーブルに対して挿入することも可能です。
INSERT INTO canada_pageviews SELECT * FROM vancouver_pageviews;
データのファイルフォーマット
INSERT INTOは以下のファイルフォーマットをサポートしています。追加したデータはテーブル定義に指定したSerDeを用いて、以下のファイルフォーマットで保存されます。
| Data format | SerDe |
|---|---|
| Avro | org.apache.hadoop.hive.serde2.avro.AvroSerDe |
| JSON | org.apache.hive.hcatalog.data.JsonSerDe |
| ORC | org.apache.hadoop.hive.ql.io.orc.OrcSerde |
| Parquet | org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe |
| Text file | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
※ CSV, TSV, custom-delimited files はサポートしていません。
INSERT INTO ... VALUEの動作を確認
今回はファイルフォーマットがParquetのテーブルを用いて検証します。
パーティションしていないテーブルの検証
最初は最もシンプルなパーティションしていないテーブルに対するINSERT INTO動作を確認します。
検証用テーブル
検証用に以下のテーブルを作成します。
-- DROP TABLE scores; CREATE EXTERNAL TABLE scores( id bigint, name string, score int, created varchar(10)) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://cm-test-0920/scores' TBLPROPERTIES ( 'has_encrypted_data'='false') ;
データの追加
作成したscoresテーブルに対して、レコードを追加します。
INSERT INTO scores (id, name, score, created) VALUES(1,'foo', 30, '2019-07-01');
追加が完了すると、Zero records returnedが返ります。
S3を確認するとファイルが生成されていることが確認できます。
更に生成したParquetファイルの中身を確認してみますと、追加したレコードの値が格納されていることが確認できます。
クエリの実行
追加したレコードを確認できました。
更にデータを追加
INSERT INTO scores (id, name, score, created) VALUES(2,'bar', 31, '2019-07-01');
S3を確認すると更にファイルが生成されていることが確認できます。つまり、INSERT INTO を実行するたびにファイルが追加されるという動作です。
更にクエリの実行
改めて、クエリを実行しますと、２レコード参照できます。
パーティションしたテーブルの検証
次はパーティションしたテーブルに対するINSERT INTO動作を確認します。
検証用テーブル
検証用に以下のテーブルを作成します。
-- DROP TABLE scores_partitioned; CREATE EXTERNAL TABLE scores_partitioned( id bigint, name string, score int) PARTITIONED BY ( created varchar(10)) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://cm-test-0920/scores_partitioned' TBLPROPERTIES ( 'has_encrypted_data'='false') ;
データの追加
作成したscores_partitionedテーブルに対して、レコードを追加します。これまでと同じように追加が完了すると、Zero records returnedが返ります。
INSERT INTO scores_partitioned (id, name, score, created) VALUES(3,'baz', 32, '2019-07-01');
S3を確認するとcreated=2019-07-01というフォルダの下にファイルが生成されていることが確認できます。つまり、パーティションキーを判別して、カラム名ありパーティションのファイルレイアウトで格納されています。
クエリの実行
追加したレコードを確認できました。
参照できたということはINSERT INTOを実行するとパーティションの追加も自動的に実行されているということになります。Glueからパーティションが追加されていることを確認できます。
更にデータの追加
今度は異なる日付（異なるパーティション）のをレコード追加します。
INSERT INTO scores_partitioned (id, name, score, created) VALUES(4,'qux', 33, '2019-07-02');
S3を確認するとcreated=2019-07-02というフォルダの下にファイルが生成されていることが確認できます。つまり、パーティションキーを判別して、別のカラム名ありパーティションのファイルレイアウトで格納されています。
更にクエリの実行
改めて、クエリを実行しますと、２レコード参照できます。つまり、２つのデータがパーティション構成でファイルレイアウトされ、かつパーティションが自動的に設定されているということです。
INSERT INTO ... SELECTの動作を確認
INSERT INTO ... VALUEの動作を確認にて作成した2つのテーブルをソーステーブルに用いて検証します。
検証テーブル
検証用テーブルもパーティション設定したテーブルを用意します。以降では、このテーブルに対してデータをINSERT INTOします。
-- DROP TABLE scores_partitioned_all; CREATE EXTERNAL TABLE scores_partitioned_all( id bigint, name string, score int) PARTITIONED BY ( created varchar(10)) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://cm-test-0920/scores_partitioned_all' TBLPROPERTIES ( 'has_encrypted_data'='false') ;
パーティションしていないテーブルからデータの追加
データの追加
非パーティションテーブルであるscoresテーブルの内容をscores_partitioned_allに追加します。これまでと同じように追加が完了すると、Zero records returnedが返ります。
INSERT INTO scores_partitioned_all SELECT * FROM scores;
S3を確認するとcreated=2019-07-01というフォルダの下に１つファイルが生成されていることが確認できます。つまり、パーティションキーを判別して、カラム名ありパーティションのファイルレイアウトで格納し、ソーステーブルのファイルは２つでしたが１つのファイルマージされています。
クエリの実行
ファイルは１つにマージされましたが、ソーステーブルと同じように2レコード参照できました。
パーティションしているテーブルからデータの追加
データの追加
パーティションテーブルであるscores_partitionedテーブルの内容をscores_partitioned_allに追加します。これまでと同じように追加が完了すると、Zero records returnedが返ります。
INSERT INTO scores_partitioned_all SELECT * FROM scores_partitioned;
S3を確認するとcreated=2019-07-01と2019-07-02という2フォルダの下にそれぞれ１つファイルが生成されていることが確認できます。つまり、パーティションキーを判別して、カラム名ありパーティションのファイルレイアウトでそれぞれ格納されています。
クエリの実行
改めて、クエリを実行しますと、4レコード参照できます。パーティション構成の異なる２つのテーブルをINSERT INTO しても、パーティションキーに従い適切にパーティション設定されることが確認できました。
INSERTしたテーブルの削除
テーブルはこれまでのテーブルと同様、テーブル及びそのパーティションは削除されますが、参照しているデータファイルはそのまま残ります。
利用費
SELECTクエリの費用と同様に、SELECTフェーズでスキャンされたバイト数に基づいて請求されます。つまり、INSERT INTO ... SELECTのSELECT実行時にスキャンしたデータファイル1TBあたり$5となります。
まとめ
今回の検証の結果以下のことが確認できました。
- INSERTするたびにデータファイルが追加される
- パーティションキーに応じてデータファイルの生成、パーティションの追加が自動化されている
- 同じパーティションキーのファイルは、INSERT INTO ... SELECTすることでマージされる
つまり、既存のテーブルの構成に従うので、INSERT INTOステートメントを実行するときは何も意識する必要がないということです。
今回は、INSERT INTOを使用して、パーティション・非パーティション形式のParquetのソーステーブルから、パーティション形式のParquetのターゲットテーブルに書き込むことができました。この新機能はプロダクションで十分に使える機能であり、AthenaによるETLプロセスの簡素化にも役立つはずです。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
